Query Tool and Utilities

Details regarding the DEACOM Query Tool and other utilities

Query Tool

The DEACOM Query Tool is a read-only SQL based tool that can access all (versions 14.10 and above) or a majority of (versions 14.09 and below) the data in the system (some tables such as the user table, dxuser, are restricted in lower versions). Any SQL query can be written and run using this tool. The results of a query can be exported to Excel for further processing if necessary. As with any SQL tool, DEACOM table and field names are required when using this tool. To locate table and field names to use in queries, use the Data Dictionary located under System > Data Dictionary. The Data Dictionary can also be accessed by right clicking a field in the results grid and provides field linkages that can be used for joining tables.

Additional notes regarding this tool include:

User access to the Query Tool is controlled by the "System -- Query Tool" security permission.
Beginning with version 14.4, queries can be published as Favorites and Previews. The process for creating favorites and previews Managing User Favorites and Previews.
The query tool can only access the three DEACOM databases for security reasons. Each time a query is run in DEACOM, a temporary user is created in order to run the query. This temporary user is limited to the DEACOM databases and any public folders. If an external database needs to be accessed via a DEACOM query, add "public" as a user with the security options for "Authenticate", "Control", "Execute", "Select", "Subscribe Query Notifications", and "View Database Definition" granted for database options.

Configuration

No specific configuration is required to use this tool.

Process

Using the Query Tool

Using the Query Tool is as easy as navigating to System > Query Tool, entering the desired query, and clicking "View" to display the results.

Queries can be saved and run later, by opening the saved query from the query tool. Query results will appear in a grid, which may be published to Favorites or Favorite with Preview. Favorites can be published to users, to allow them to view data, their security may otherwise prevent them from accessing.

The query tool can also be used to create a view, using the Save as View button. Once a query has been saved as a view, the results will be maintained in a table, named the same as the view. This view can then be queried, or used in other system functions, such as triggers.

Note on IDS:

_id – This is a unique integer primary key identifier that increases by 1 for every row of data. This can be found in most tables (only 15 tables are exceptions at the time of writing). Example: pr_id is the unique identifier on the dmprod (Item Master) table.
_*id – This is a reference and link to a primary key on another table. This is not unique. Example: cu_prid is the link from the dmcust (Customer Parts) table to the table dmprod (Item Master).
_guid – This is a unique identifier field that is used on just a few tables. The SQL data type is uniqueidentifier and is a system generated key. Example: c2_guid is the unique ID on the dxcaption2 (Captions) table in addition to the c2_id being the integer primary key unique ID.
_*guid – This is a link to a unique identifier field on another table. This is not unique. Example: d3_c2guid is the link from the dmd3 (UDF pick-list options) table to the table dxcaption2 (Captions).

Examples

Examples of useful queries are included below, organized by functional area.

Item Masters

All active items from Item Master

SELECT pr_codenum, pr_active FROM dmprod WHERE pr_active = 1

All active items from Item Master along with their Category

SELECT pr_codenum, pr_descrip, ca_name FROM dmprod, dmcats WHERE pr_active = 1 AND pr_caid = ca_id

All active items from Item Master that are flagged as "Hazardous"

SELECT pr_codenum, pr_hazflag FROM dmprod WHERE pr_active = 1 AND pr_hazflag = 1

Information on Customer Part Cross References

SELECT cu_id, pr_codenum, cu_codenum, cu_prid, cu_salunid, cu_salfact, bi_name from dmcust, dmbill, dmprod WHERE bi_id = cu_biid AND cu_prid = pr_id

Bills of Materials

All active BOMs and BOM lines

SELECT p1.pr_codenum AS BOM, p2.pr_codenum AS PartNo, p2.pr_descrip AS Description, bo_quant FROM dmbom, dmrev, dmprod p1, dmprod p2 where bo_bomfor = p1.pr_id and bo_prid = p2.pr_id and bo_reid = re_id and re_status = 'c'

All Parts that have specific Routing notes entered during Edit Routing Sequence.

In this example, the note is "Archway"

SELECT pr_codenum, re_name, ro_name, CAST(r2_notes AS CHAR(200)) FROM dmprod, dmrev, dmrout3, dmrout, dmrout2 WHERE re_prid = pr_id AND re_id = r3_reid AND ro_id = r3_roid AND r2_roid = ro_id AND re_status = 'c' AND CAST(r2_notes AS CHAR(200)) = 'Archway'

All active BOMs with their Revision name, Routing group, and status of the "Default", "Active", "Lab Only", and "Regulatory" flags

SELECT pr_codenum as part_Number, re_name as revision_number, ro_name as routing_group, re_default as default_, re_active as active, re_private as lab_only, re_regulat as regulatory from dmprod, dmrev, dmrout, dmrout3 where re_prid = pr_id and r3_roid = ro_id and r3_reid = re_id and re_status='c'

Quality Control

All active Quality Control Tests that contain the word 'acid'

SELECT * FROM dmqc3 WHERE q3_active = 1 AND q3_name like '% acid %'

All active Quality Control Tests from all Quality Control Groups

SELECT qc_name, q3_name, q2_min, q2_max FROM dmqc2, dmqc, dmqc3 WHERE qc_id = q2_qcid AND q2_q3id = q3_id AND q3_active = 1 AND qc_status = 'c'

All Quality Control Groups that contain a certain Quality Control Test.

Users will need to know the Quality Control Test ID (q3_id)
Locate the q3_id for a Quality Control Test by clicking "View" > "Show All Fields" in the Quality Control Tests form
In this example, the q3_id is 1

SELECT qc_name FROM dmqc2, dmqc, dmqc3 WHERE qc_id = q2_qcid AND q2_q3id = q3_id AND q3_id IN (1)

All Parts and the Quality Control Tests listed on their Item Masters , for use in versions 14.3 and higher

SELECT pr_codenum, qc_name FROM dmprod, dmqc6, dmqc WHERE pr_id = q6_recid AND q6_table = 'dmprod' AND q6_qcid = qc_id

All Formulas and their Quality Control Tests, for use in versions 14.3 and higher

SELECT pr_codenum, re_name, qc_name FROM dmprod, dmrev, dmqc, dmqc6 WHERE re_prid = pr_id AND q6_qcid = qc_id AND q6_table = 'dmrev' AND re_status = 'c' AND q6_recid = re_id

Production

All Jobs with a specific user-defined field

In this example, the ID number for the user-defined field is 12.

SELECT jo_jobnum, jo_descrip, d2_value FROM dtjob LEFT JOIN dtd2 ON jo_jobnum = d2_recid AND d2_d1id = 12

Purchasing

All Vendors added between specified dates

SELECT lo_date, lo_time, ve_name FROM dxlog, dmvend WHERE lo_recid = ve_id AND lo_date >= ‘01/01/2012’ AND lo_date <= '12/31/2012' AND lo_table = 'dmvend'

Vendors with a specific user-defined field.

SELECT ve_name, ve_street, ve_street2, ve_city, ve_state, ve_zip, ve_contact, ve_phone, d2_value AS 'XX' FROM dmd1 LEFT JOIN dtd2 ON d1_id=d2_d1id LEFT JOIN dmvend ON d2_recid=ve_id WHERE d1_table='dmvend' AND d1_title = 'XX'

All Vendors that have ordered in a given year

SELECT ve_name FROM dmvend, dttpur WHERE dmvend.ve_id = dttpur.tp_veid AND dttpur.tp_date > '01/01/2013' AND dttpur.tp_date < '12/31/2013'

Sales

All active Ship-to Companies and their Sales Rep assignments, for use in versions 14.3 and earlier

SELECT bi_name AS Bill_to, sh_name AS Ship_to, sh_street, sh_street2, sh_city, sh_state, sh_zip, RTRIM(sm_fname) + ' ' + RTRIM(sm_lname) AS Salesman, s2_pct AS 'Percent'

FROM dmbill, dmsman, dmsman2, dmship

WHERE bi_active=1 AND sh_active = 1 AND s2_smid = sm_id AND s2_recid = sh_id AND s2_table = 'dmship' AND bi_id = 'sh_biid'

All active Ship-to Companies and their Sales Rep assignments , for use in versions 14.4 and later

SELECT bi_name AS Bill_to, sh_name AS Ship_to, sh_street, sh_street2, sh_city, sh_state, sh_zip, RTRIM(sm_fname) + ' ' + RTRIM(sm_lname) AS Salesman, s2_pct AS 'Percent'

FROM dmbill, dmsman, dmsman2, dmship, dmbillship

WHERE bi_active=1 AND sh_active = 1 AND s2_smid = sm_id AND s2_recid = sh_id AND s2_table = 'dmship' AND bs_biid = bi_id AND bs_shid = 'sh_id'

Sales Rep assignments for Bill-to Companies

SELECT bi_name AS BILLTO, RTRIM(RTRIM(sm_fname) + ' ' + sm_lname) AS SALESREP FROM dmbill JOIN dmsman2 ON s2_table = 'dmbill' AND s2_recid = bi_id JOIN dmsman ON sm_id = s2_smid WHERE sm_active = 1 AND bi_active = 1

Brokers assigned to Bill-to Companies

SELECT bi_name AS BILLTO, br_name AS BROKER FROM dmbill JOIN dmbrok ON bi_brid = br_id WHERE br_active = 1 AND bi_active = 1

Brokers assigned to Ship-to Companies

SELECT sh_name AS SHIPTO, br_name AS BROKER FROM dmship JOIN dmbrok ON sh_brid = br_id WHERE br_active = 1 AND sh_active = 1

The last ordered date for any active customers that have not ordered within the last year

SELECT ve_name, MAX(tp_date) FROM dmvend, dttpur WHERE ve_active = 1 AND ve_id = tp_veid GROUP BY ve_name HAVING MAX(tp_date) < GETDATE() - 365

The last shipment date for all customers

SELECT MAX(to_shipped), bi_name, sh_name FROM dmbill, dttord, dmship WHERE to_biid = bi_id AND to_shid = sh_id GROUP BY bi_name, sh_name

The Sales Order Detail for payments for a given date range

SELECT ca_date, ca_postref, ca_check, bi_name, sh_name, c2_ordnum, c2_debits, c2_credits from dtcash, dtcash2, dmship, dmbill, dttord where ca_postref = c2_postref and c2_shid = sh_id and to_biid = bi_id AND to_ordnum = c2_ordnum AND to_status = 'c' and ca_arap = 'ar' and ca_date >='01/01/2013' and ca_date <= '12/31/2013' and c2_type='cash'

Select all Bill-to Companies created in Q1 and the Sales Rep on the first Sales Order

SELECT MIN(to_ordnum), bi_name, sm_fname, sm_lname FROM dttord, dmbill, dxlog, dmsman, dmsman2 WHERE lo_date >= '01/01/2014' AND lo_date <= '03/31/2014' AND lo_table = 'dmbill' AND to_biid = bi_id AND lo_recid = bi_id AND s2_table = 'dttord' AND s2_recid = to_id AND sm_id = s2_smid GROUP BY bi_name, sm_fname, sm_lname

Select all Bill-to Companies that went from inactive to active in Q1 with the assigned Sales Rep

SELECT mo_date, bi_name, sm_fname, sm_lname

FROM dxmod

INNER JOIN dmbill ON mo_recid = bi_id

INNER JOIN dmsman2 ON s2_table = 'dmbill' AND s2_recid = bi_id

INNER JOIN dmsman ON s2_smid = sm_id

WHERE mo_table = 'dmbill' AND mo_field = 'bi_active' AND mo_oldval = 'No'

AND mo_date BETWEEN '01/01/2014' AND '03/31/2014'

All active Bill-to Companies with total sales year to date compared to their sales quota

SELECT bi_id AS bill_to_ID, bi_name AS Bill_to_company, sm_id AS salesman_ID, RTRIM(sm_fname) + ' ' + RTRIM(sm_lname) AS salesman, s2_name AS price_type, s1_name AS territory, bi_quota AS quota, SUM(to_totdue)

FROM dmbill, dmsman, dmsman2, dmso2, dmso1, dttord

WHERE bi_active = 1

AND dmbill.bi_s2id = dmso2.s2_id

AND dmbill.bi_id = dmsman2.s2_recid

AND dmsman2.s2_smid = dmsman.sm_id

AND dmbill.bi_s1id = dmso1.s1_id

AND dmbill.bi_id = dttord.to_biid

AND dttord.to_paydate BETWEEN '01/01/2014' AND '08/11/2014'

AND dmsman2.s2_table = 'dmbill'

GROUP BY bi_id, bi_name, sm_id, RTRIM(sm_fname) + ' ' + RTRIM(sm_lname), s2_name, s1_name, bi_quota

Sales Orders with engineering information for an engineered item

SELECT bi_name, pr_codenum, or_user1 AS partnum, in_span, in_height FROM dttord, dtord, dteng, dmbill, dmprod WHERE to_id = or_toid AND or_id = in_orid AND or_prid = pr_id AND to_biid = bi_id AND to_status = 'c' AND to_ordnum = 20129999900

Accounting

Sales tax charges for Tax Region 1 during a given time period, including Ship-to City and State

SELECT bi_name AS Bill_to_Name, sh_name AS Ship_to_Name, sh_city AS Ship_to_City, sh_state AS Ship_to_State, to_ordnum AS Order_Number, to_orddate AS Order_Date, to_shipped AS Shipped_Date, wa_name AS Warehouse, ta_name AS Tax_Region, or_exten AS Tax_Amount

FROM dttord, dmbill, dmship, dmware, dtord, dmtax

WHERE to_biid = bi_id

AND to_shid = sh_id

AND to_waid = wa_id

AND to_id = or_toid

AND or_taid = ta_id

AND to_status = 'c'

AND to_statax = or_taid

AND to_orddate BETWEEN '01/01/2014' AND '03/10/2014'

AND or_exten <> 0

Sales tax charges for Tax Region 2 during a given time period, including Ship-to City and State

FROM dttord, dmbill, dmship, dmware, dtord, dmtax

WHERE to_biid = bi_id

AND to_shid = sh_id

AND to_waid = wa_id

AND to_id = or_toid

AND or_taid = ta_id

AND to_status = 'c'

AND to_loctax = or_taid

AND to_orddate BETWEEN '01/01/2014' AND '03/10/2014'

AND or_exten <> 0

All General Ledger records where the action is "Order invoiced" and the user who invoiced the order

When using the example below, replace 'Sandbox147System' with the system database name

SELECT dtgl.*, us_fname, us_lname

FROM dtgl

LEFT JOIN Sandbox147System.dbo.dxuser

ON gl_usid = us_id

WHERE gl_action = 'Order invoiced'

Miscellaneous

All grids that contain a specific field

When using the example below, replace 'field name' with the 'column name'

SELECT br_title FROM dxbrow LEFT JOIN dxbrow2 on b2_brid = br_id where b2_field = 'field name'

View the start and end dates for all Favorites and Previews

When using the example below, replace 'x' with the desired User ID number

SELECT fv_caption, d2_start, d2_end, fv_preview FROM dxfav, dmdash2 WHERE fv_d2id = d2_id AND fv_usid = x

FAQ & Diagnostic Tips

Why is the Sales Rep ID (bi_smid) field zero in the dmbill table when I have a Sales Rep assigned to my customer?

DEACOM now allows for multiple Sales Reps to be assigned to a customer record. Saleman ID is no longer stored in that field. This information is stored in the Sales Rep (dmsman) and Sales Rep Assignments (dmsman2) tables. Below is a query to show Ship-to Sales Reps assignments:

SELECT bi_name AS Bill_to, sh_name AS Ship_to, RTRIM(sm_fname) + ' ' + RTRIM(sm_lname) AS Salesman, s2_pct AS 'Percent' FROM dmbill, dmsman, dmsman2, dmship WHERE bi_active=1 AND sh_active = 1 AND s2_smid = sm_id AND s2_recid = sh_id AND s2_table = 'dmship' AND bi_id = sh_biid

How can I link terms table to show accounting terms name instead of Bill-to terms id when querying the dmbill table?

Add the dmterm table to the query and link bi_teid=te_id. The te_name field can be added to display the Accounting Terms Name.

SELECT bi_name, te_name FROM dmbill, dmterm WHERE bi_teid=te_id AND bi_active = 1

Why do I get the following system prompt when running the query tool? "Please Note: There is an error in the query text: SQL SELECT statement is invalid."

For classic versions 14.06 and lower: Make sure that there is an extra space on the end of each line. The Query tool does not treat the carriage return as a space.

For classic and web versions 14.07 and above: The Query Tool recognizes carriage returns. It is NOT necessary to put a space at the end of each line of the query in any versions 14.07 and higher.

How is the PO header (dttpur) and PO detail (dtpur) linked?

These tables are linked by two fields (tp_id = pu_tpid and tp_purnum = pu_purnum).

Can you run a query to show when a user logs in and out of DEACOM?

The dxonoff table holds records of when a user logs in and out of DEACOM. For versions 14.10 and above, all system tables can be accessed with the Query Tool, as long as the database name and owner are specified (e.g. SELECT * FROM MyCompanySystem.dbo.dxcomp4). This applies to System tables like dxuser and dxonoff which are accessible via the Query Tool. If you still have trouble accessing the desired table, contact your SQL administrator for assistance.

How do I handle the following error?

"An error occurred processing a SQL command -- Cannot execute as the database principal because the principal "XXXXXX_QueryTool" does not exist, this type of principal cannot be impersonated, or you do not have permission."

To fix this error, perform the following in SQL Server Management Studio:

Expand the Company database.
Expand the Security folder.
Expand the Users folder.
Delete the Query Tool specified in the system prompt.

Using Externals

Externals is a list of installed custom programs that are available to be run. These programs execute functions outside the scope of the normal DEACOM system. Since externals may modify DEACOM data in ways that are not compatible with the main application, they should be tested in a training environment, and the results validated, before being installed and run in a production environment.

DEACOM version updates do not automatically update externals. During the system update process, externals should be tested when the proposed update is present in the training environment, and the results validated. If the external no longer functions as desired in the updated environment, contact DEACOM Support to schedule programming time to have the program updated. The maintenance of custom externals is a billable project.

Deacom developed externals are typically found in the Addins folder and accessed through Tools > Externals. They are run manually by an authorized user. Most of these externals are developed in accordance with specific customer requirements. Examples would include a Bill of Lading combiner to allow users to select multiple sales orders and print a consolidated system report (Bill of Lading). Note this functionality is now available in the .NET interface of DEACOM with the Combined Reports feature. Other externals may be of a utility nature such as the external for migrating Report Layouts from Classic to web or moving Units of Measure from test system to production system during an update. Please contact Deacom Support or Customer Success if you think you have requirements for a DEACOM External.

In addition, Scripts may be added to Externals and will run when the External is run. The process is detailed below. Scripts should be tested in a training environment, and the results validated, before being installed and run in a production environment.

Configuration

No specific configuration required.

Process

Running Externals

Locate the external files. The general method of delivering these files to the customer is via e-mail or secure FTP.
Download and unzip the external files to the "addins" folder located in the main DEACOM application folder.
In the main DEACOM application navigate to Tools > Externals
Click the "Add" button to launch the Edit External Program form.
Enter the "Name" which is a required field. Program name must match the program in the "addins" folder exactly. Enter the Program name only, do not include the extension (.fxp, .fxs, .dll).
Click the "Save" button.
Click the "Next" button to add another external and repeat steps 1-6.
Click the "Exit" button when done.
Select the external program you wish to run in the list and click the "Run" button.
When running externals, the system will present a prompt asking the user to confirm they wish to run the program. When the external has completed running, the system will display a prompt indicating the external has finished.

There are three potential file extensions that may be visible when adding an external: .dll, .fxp, and .fxs . Each extension is treated the same within DEACOM and does not require additional setup.

Running Externals with Scripts enabled

Navigate to Tools > Externals.
Click the "Add" button.
Enter the appropriate name on the Edit External form.
Select "Script" for Type.
Enter the appropriate Script in the "Script" field. on the form. The security setting "Tools--edit scripts" is needed to access this field.
Save and exit the form.
Select the External from the list and hit the "Run" button to complete the process.

Note: Externals and Externals with Scripts enabled may be set to run automatically via Scheduled Events.

FAQ & Diagnostic Tips

Tip:If a system prompt stating “The file does not exist.” is displayed, please review the following:

Confirm proper drive mapping exists
Confirm program is in the Addins folder
Confirm name of program matches external name exactly. Do not include .fxp, .fxs, or .dll extension.
Confirm the external files are unzipped

I am receiving an OLE error when attempting to run the external.

Close and then restart the main DEACOM application and then re-run the external. This should resolve the problem. The OLE error is usually caused when the external and the DEACOM application are different versions. Restarting the application clears any temporary files or entries that were left behind when running the mismatched external the first time.